package readExcel;

import java.io.FileInputStream;
import java.io.InputStream;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadExcelTest {
	/**
	 * 获取工程目录的路径
	 * @return
	 */
	
	public static String getPath(){
		String realPath = ReadExcelTest.class.getResource("").toString();
		int index = realPath.lastIndexOf("bin");
		//System.out.println(realPath.substring(0,index));
		String path = realPath.substring(0,index).replaceAll("file:/", "");
		return path;
	}
	
	/**
	 * 	给一个EXCEL文件，返回一个EXCEL文件数据的二维数组
	 * 	string[0][0]第一行第一个数据，string[0][1]第一行第二个数据.....
	 * filePath  文件路径
	 * sheetNum  这个excel文件里的第几个sheet，第一个是0
	*/
	public static String[][] getAllData(String filePath,int sheetNum){
		String[][] allData = null;
		try {
			InputStream ins = new FileInputStream(filePath);
			Workbook wb = Workbook.getWorkbook(ins);
			Sheet sheet = wb.getSheet(sheetNum);
			int row = sheet.getRows();
			int col = sheet.getColumns();
			allData = new String[row][col];
			
			//将excel中的数据加到allData数据中
			for(int i = 0;i < row;i++){
				for(int j = 0; j < col;j++){
					//列在前，行在后
					Cell cell = sheet.getCell(j, i);
					allData[i][j] = cell.getContents();
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return allData;
	}
	
	/**
	 * 获得总行数
	 * @param args
	 */
	public static int getRows(String filePath,int sheetNum){
		InputStream ins;
		Workbook wb;
		int row = 0;
		try{
			ins = new FileInputStream(filePath);
			wb = Workbook.getWorkbook(ins);
			Sheet sheet = wb.getSheet(sheetNum);
			row = sheet.getRows();
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		return row;
	}
	/**
	 * 获取总列数
	 * @param filePath
	 * @param sheetNum
	 * @return
	 */
	public static int getColumns(String filePath,int sheetNum){
		InputStream ins;
		Workbook wb;
		int col = 0;
		try{
			ins = new FileInputStream(filePath);
			wb = Workbook.getWorkbook(ins);
			Sheet sheet = wb.getSheet(sheetNum);
			col = sheet.getColumns();
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		return col;
	}
	/**
	 * 获得指定单元格的数值
	 * @param filePath
	 * @param sheetNum
	 * @param row
	 * @param col
	 * @return
	 */
	public static String getText(String filePath,int sheetNum,int row,int col){
		InputStream ins;
		Workbook wb;
		String text = "";
		try{
			ins = new FileInputStream(filePath);
			wb = Workbook.getWorkbook(ins);
			Sheet sheet = wb.getSheet(sheetNum);
			Cell cell = sheet.getCell(col-1, row-1);
			text = cell.getContents();
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		return text;
	}
	
	public static void main(String[] args) {
		//System.out.println(ReadExcelTest.class.getResource(""));
		//file:/D:/Users/Workspaces/MyEclipse%208.5/leadingsoft/bin/readExcel/
		System.out.println("**************"+getPath());
		try {
			String[][] excelData = getAllData(getPath()+"PPS.xls",0);
			int row = getRows(getPath()+"PPS.xls",0);
			String text = getText(getPath()+"PPS.xls",0,12,1);
			
			System.out.println("共有："+row+"行");
			System.out.println("共有："+excelData.length+"行");
			System.out.println("单元格的值："+text);
			
			for(int i = 0;i<excelData.length;i++){
				String[] rowData = excelData[i];
				for(int j = 0;j<rowData.length;j++){
					System.out.print(excelData[i][j]+"\t");
				}
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
		
